package com.app.TCES.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.app.TCES.domain.Indicators;
import com.app.TCES.domain.Teacher;
import com.app.TCES.domain.TeacherSet;

@Mapper
public interface TeacherMapper {

	@Update("update teacher set password = #{teacher.password} where tchId = #{teacher.tchId} ")
	void tchCGPW(@Param("teacher")Teacher teacher);

	@Select("select courseId from course where courseName = #{teacherSet.courseName}")
	int getCourseId(@Param("teacherSet")TeacherSet teacherSet);

	@Select("select deptId from department where deptName = #{teacherSet.deptName}")
	int getDeptId(@Param("teacherSet")TeacherSet teacherSet);

	@Insert("insert into teacher(tchId,tchName,deptId) values(#{teacherSet.tchId},#{teacherSet.tchName},#{deptId})")
	void insertTeacher(@Param("deptId")int deptId, @Param("teacherSet")TeacherSet teacherSet);

	@Insert("insert into teacher_course(tchId,courseId) values(#{teacherSet.tchId},#{courseId})")
	void insertTeacherCourse(@Param("courseId")int courseId, @Param("teacherSet")TeacherSet teacherSet);

	@Select("select count(*) from teacher where tchId = #{teacherSet.tchId}")
	int selectTeacher( @Param("teacherSet")TeacherSet teacherSet);

	@Delete("delete from teacher where tchId = #{tchId}")
	void deleteTeacher(@Param("tchId")int tchId);

	@Update("update teacher set deptId = #{deptId} where tchId = #{tchId}")
	void updateTeacherDN(@Param("deptId")int deptId, @Param("tchId")int tchId);

	@Select("Select deptId from department where deptName = #{value}")
	int getDeptId1(@Param("value")String value);

	@Select("select courseId from course where courseName = #{value}")
	int getCourseId1(@Param("value")String value);

	@Update("update teacher_course set courseId = #{courseId} where tchId = #{rid}")
	void updateTeacherCN(@Param("courseId")int courseId,@Param("rid")int rid);

	@Select("select t.tchId,t.tchName,c.courseName,d.deptName from teacher t,course c, teacher_course tc,department d where d.deptId = t.deptId and t.tchId = tc.tchId and tc.courseId = c.courseId and(t.tchId like '%${text}%' || t.tchName like '%${text}%' || c.courseName like '%${text}%' || d.deptName like '%${text}%') limit #{tmp},#{limit}")
	List<TeacherSet> getTeacherFuzzyQ(@Param("text")String text, @Param("tmp")int tmp,@Param("limit") Integer limit);
 
	@Select("select t.tchId,t.tchName,c.courseName,d.deptName from teacher t,course c, teacher_course tc,department d where d.deptId = t.deptId and t.tchId = tc.tchId and tc.courseId = c.courseId and d.deptName = #{deptName} and (t.tchId like '%${text}%' || t.tchName like '%${text}%' || c.courseName like '%${text}%' ) limit #{tmp},#{limit}")
	List<TeacherSet> getTeacherFuzzyQ1(@Param("text")String text, @Param("deptName")String deptName, @Param("tmp")int tmp,@Param("limit") Integer limit);
	
    @Select("select count(*) from teacher t,course c, teacher_course tc,department d where d.deptId = t.deptId and t.tchId = tc.tchId and tc.courseId = c.courseId and(t.tchId like '%${text}%' || t.tchName like '%${text}%' || c.courseName like '%${text}%' || d.deptName like '%${text}%')")
	int gettol2(@Param("text")String text);

    @Select("select count(*) from teacher t,course c, teacher_course tc,department d where d.deptId = t.deptId and t.tchId = tc.tchId and tc.courseId = c.courseId and d.deptName = #{deptName} and (t.tchId like '%${text}%' || t.tchName like '%${text}%' || c.courseName like '%${text}%' )")
	int gettol3(@Param("text")String text, @Param("deptName")String deptName);

    @Select("select count(*) from  teacher t,course c, teacher_course tc,department d where d.deptId = t.deptId and t.tchId = tc.tchId and tc.courseId = c.courseId and t.tchName = #{tchName}")
    int selectByTchName(@Param("tchName")String tchName);
    
    @Insert("insert into teacher(tchId,tchName,deptId) values(#{teacherResord.tchId},#{teacherResord.tchName},#{deptId}) ")
    void  addTeacher(@Param("teacherResord")TeacherSet teacherResord,@Param("deptId")int deptId);
    
    @Insert("insert into teacher_course(tchId,courseId) values(#{teacherResord.tchId},#{courseId})")
    void  addTeacherZJ(@Param("teacherResord")TeacherSet teacherResord,@Param("courseId")int courseId);

    @Select("select deptId from teacher where tchName = #{tchName}")
	int getDeptId2(String tchName);

    @Select("select * from teacher t,department d where t.deptId = d.deptId and d.deptId = #{deptId} and t.tchId != #{tchId}; ")
	List<TeacherSet> selectTeacher1(@Param("deptId")int deptId, @Param("tchId")int tchId);

    @Select("select tchId from teacher where tchName=#{tchName}")
	int getTchId(@Param("tchName")String tchName);

    @Select("select * from indicators where eva_teacher_id = #{eva_teacher_id}  and tchId = #{tchId}")
	Indicators selectIndicators(@Param("eva_teacher_id")int eva_teacher_id, @Param("tchId")int tchId);

    @Insert("insert into indicators(score,tchId,eva_teacher_id) values(#{score},#{tchId},#{eva_teacher_id})")
	void TonghangDaFen(@Param("score")int score,@Param("eva_teacher_id") int eva_teacher_id,@Param("tchId")int tchId);

    @Select("select avg(score) from indicators where eva_teacher_Id is null and tchId = #{tchId}")
	int selectScore(@Param("tchId")int tchId);
    
    @Select("select avg(score) from indicators where eva_student_Id is null and tchId = #{tchId}")
	int selectThScore(@Param("tchId")int tchId);

    @Update("update teacher set password='123456' where tchId = #{tchId}")
	void TeacherRP(@Param("tchId")int tchId);

}
